Understanding the Connected Layer of ADO.NET

Recall that the connected layer of ADO.NET allows you to interact with a database using the connection, command, and data reader objects of your data provider. You have already used these objects in the previous DataProviderFactory application, now you’ll walk through the process again, this time using an expanded example. You need to perform the following steps when you wish to connect to a database and read the records using a data reader object:

1. Allocate, configure, and open your connection object.
2. Allocate and configure a command object, specifying the connection object as a constructor argument or with the Connection property.
3. Call ExecuteReader() on the configured command class.
4. Process each record using the Read() method of the data reader.

To get the ball rolling, create a new Console Application named AutoLotDataReader and import the System.Data and System.Data.SqlClient namespaces. Here is the complete code within Main(); an analysis will follow:

class Program
{
    static void Main(string[] args)
    {
        Console.WriteLine("***** Fun with Data Readers *****\n");

        // Create an open a connection.
        using(SqlConnection cn = new SqlConnection())
        {
            cn.ConnectionString =
                @"Data Source=(local)\SQLEXPRESS;Integrated Security=SSPI;" +
                    "Initial Catalog=AutoLot";
            cn.Open();

            // Create a SQL command object.
            string strSQL = "Select * From Inventory";
            SqlCommand myCommand = new SqlCommand(strSQL, cn);

            // Obtain a data reader a la ExecuteReader().
            using(SqlDataReader myDataReader = myCommand.ExecuteReader())
            {
                // Loop over the results.
                while (myDataReader.Read())
                {
                    Console.WriteLine("-> Make: {0}, PetName: {1}, Color: {2}.",
                        myDataReader["Make"].ToString(),
                        myDataReader["PetName"].ToString(),
                        myDataReader["Color"].ToString());
                }
            }
        }
        Console.ReadLine();
    }
}

Working with Connection Objects

The first step to take when working with a data provider is to establish a session with the data source using the connection object (which, as you recall, derives from DbConnection). .NET connection objects are provided with a formatted connection string; this string contains a number of name/value pairs, separated by semicolons. You use this information to identify the name of the machine you wish to connect to, required security settings, the name of the database on that machine, and other data provider–specific information.

As you can infer from the preceding code, the Initial Catalog name refers to the database you want to establish a session with. The Data Source name identifies the name of the machine that maintains the database. Here, (local) allows you to define a single token to specify the current local machine (regardless of the literal name of said machine), while the \SQLEXPRESS token informs the SQL server provider that you are connecting to the default SQL Server Express edition installation (if you created AutoLot on a full version of Microsoft SQL Server on your local computer, specify Data Source=(local)).

Beyond this, you can supply any number of tokens that represent security credentials. Here, you set the Integrated Security to SSPI (equivalent to true), which uses the current Windows account credentials for user authentication.

Note Look up the ConnectionString property of your data provider’s connection object in the .NET Framework 4.0 SDK documentation to learn more about each name/value pair for your specific DBMS.

Once you establish your construction string, you can use a call to Open() to establish a connection with the DBMS. In addition to the ConnectionString, Open(), and Close() members, a connection object provides a number of members that let you configure additional settings regarding your connection, such as timeout settings and transactional information. Table 21-5 lists some (but not all) members of the DbConnection base class.

Table 21-5. Members of the DbConnection Type

The properties of the DbConnection type are typically read-only in nature and are only useful when you wish to obtain the characteristics of a connection at runtime. When you wish to override default settings, you must alter the construction string itself. For example, the connection string sets the connection timeout setting from 15 seconds to 30 seconds:

static void Main(string[] args)
{
    Console.WriteLine("***** Fun with Data Readers *****\n");

    using(SqlConnection cn = new SqlConnection())
    {
        cn.ConnectionString =
            @"Data Source=(local)\SQLEXPRESS;" +
            "Integrated Security=SSPI;Initial Catalog=AutoLot;Connect Timeout=30";
        cn.Open();

        // New helper function (see below).
        ShowConnectionStatus(cn);
    ...
}

In the preceding code, you pass your connection object as a parameter to a new static helper method in the Program class named ShowConnectionStatus(), which you implement as follows:

static void ShowConnectionStatus(SqlConnection cn)
{
    // Show various stats about current connection object.
    Console.WriteLine("***** Info about your connection *****");
    Console.WriteLine("Database location: {0}", cn.DataSource);
    Console.WriteLine("Database name: {0}", cn.Database);
    Console.WriteLine("Timeout: {0}", cn.ConnectionTimeout);
    Console.WriteLine("Connection state: {0}\n", cn.State.ToString());
}

While most of these properties are self-explanatory, the State property is worth special mention. You can assign this property any value of the ConnectionState enumeration:

public enum ConnectionState
{
    Broken, Closed,
    Connecting, Executing,
    Fetching, Open
}

However, the only valid ConnectionState values are ConnectionState.Open and ConnectionState.Closed (the remaining members of this enum are reserved for future use). Also, it is always safe to close a connection where connection state is currently ConnectionState.Closed.

Working with ConnectionStringBuilder Objects

Working with connection strings programmatically can be cumbersome because they are often represented as string literals, which are difficult to maintain and error-prone at best. The Microsoftsupplied ADO.NET data providers support connection string builder objects, which allow you to establish the name/value pairs using strongly typed properties. Consider the following update to the current Main() method:

static void Main(string[] args)
{
    Console.WriteLine("***** Fun with Data Readers *****\n");

    // Create a connection string via the builder object.
    SqlConnectionStringBuilder cnStrBuilder =
        new SqlConnectionStringBuilder();
    cnStrBuilder.InitialCatalog = "AutoLot";
    cnStrBuilder.DataSource = @"(local)\SQLEXPRESS";
    cnStrBuilder.ConnectTimeout = 30;
    cnStrBuilder.IntegratedSecurity = true;

    using(SqlConnection cn = new SqlConnection())
    {
        cn.ConnectionString = cnStrBuilder.ConnectionString;
        cn.Open();

        ShowConnectionStatus(cn);
...
    }
    Console.ReadLine();
}

In this iteration, you create an instance of SqlConnectionStringBuilder, set the properties accordingly, and obtain the internal string using the ConnectionString property. Also note that you use the default constructor of the type. If you so choose, you can also create an instance of your data provider’s connection string builder object by passing in an existing connection string as a starting point (this can be helpful when you read these values dynamically from an App.config file). Once you have hydrated the object with the initial string data, you can change specific name/value pairs using the related properties, as in this example:

static void Main(string[] args)
{
    Console.WriteLine("***** Fun with Data Readers *****\n");

    // Assume you really obtained the cnStr value from a *.config file.
    string cnStr = @"Data Source=(local)\SQLEXPRESS;" +
        "Integrated Security=SSPI;Initial Catalog=AutoLot";

    SqlConnectionStringBuilder cnStrBuilder =
        new SqlConnectionStringBuilder(cnStr);

    // Change timeout value.
    cnStrBuilder.ConnectTimeout = 5;
...
}

Working with Command Objects

Now that you understand better the role of the connection object, the next order of business is to check out how to submit SQL queries to the database in question. The SqlCommand type (which derives from DbCommand) is an OO representation of a SQL query, table name, or stored procedure. You specify the type of command using the CommandType property, which can take any value from the CommandType enum:

public enum CommandType
{
    StoredProcedure,
    TableDirect,
    Text // Default value.
}

When you create a command object, you can establish the SQL query as a constructor parameter or directly by using the CommandText property. Also when you create a command object, you need to specify the connection you want to use. Again, you can do so as a constructor parameter or by using the Connection property. Consider this code snippet:

// Create command object via ctor args.
string strSQL = "Select * From Inventory";
SqlCommand myCommand = new SqlCommand(strSQL, cn);

// Create another command object via properties.
SqlCommand testCommand = new SqlCommand();
testCommand.Connection = cn;
testCommand.CommandText = strSQL;

Realize that at this point that you have not literally submitted the SQL query to the AutoLot database, but instead prepared the state of the command object for future use. Table 21-6 highlights some additional members of the DbCommand type.

Table 21-6. Members of the DbCommand Type